﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;


using DTO;
using DAL;

namespace BLL
{
    public class ImportHandler
    {
        #region public properties
        public delegate void ProgressWork(float percentage);
        private static event ProgressWork Reading;
        #endregion

        #region private properties
        //private static OleDbConnection conn;
        #endregion

        public event ProgressWork ReadProgress
        {
            add
            {
                Reading += value;
            }
            remove
            {
                Reading -= value;
            }
        }

        public virtual void onReadProgress(float percentage)
        {
            if (Reading != null)
                Reading(percentage);
        }


        #region sGetExcelName(string sFilePath) ReturnType->ArrayString
        public String[] sGetExcelSheetNames(string sFilePath)
        {
            //string sConnPath = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
            string sConnPath = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Extended Properties=\"Excel {3};HDR=YES\"";

            System.IO.FileInfo fi = new System.IO.FileInfo(sFilePath);
            if (fi.Extension.Equals(".xls"))
            {
                // For Excel Below 2007 Format
                sConnPath = string.Format(sConnPath,
                           "Jet", "4.0", sFilePath, "8.0");
            }
            else if (fi.Extension.Equals(".xlsx"))
            {
                // For Excel 2007 File  Format
                sConnPath = string.Format(sConnPath,
                           "Ace", "12.0", sFilePath, "12.0");
            }

            OleDbConnection conn = new OleDbConnection(sConnPath);

            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                foreach (DataRow row in dt.Rows)
                {
                    if (!row["TABLE_NAME"].ToString().Contains("Database"))
                    {
                        excelSheets[i] = row["TABLE_NAME"].ToString().Replace("$", "").Replace("'", "");
                        i++;
                    }
                }
                return excelSheets;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
        #endregion       





    }
}
